﻿using MySql.Data.MySqlClient;
using System;
using System.Data;
using System.Data.Common;

namespace MvcApplication.Common
{
    public class ADO
    {
        static readonly int CommandTimeOut = 600;

        /// <summary>
        /// 执行SQL语句,返回结果集
        /// </summary>
        /// <param name="connection">数据库连接</param>
        /// <param name="transaction">事务</param>
        /// <param name="commandType">命令类型(存储过程,命令文本, 其它.)</param>
        /// <param name="commandText">SQL语句或存储过程名称</param>
        /// <param name="parms">查询参数</param>
        /// <returns>返回结果集</returns>
        protected static DataSet ExecuteDataSet(DbConnection connection, DbCommand command, DataAdapter adapter, DbTransaction transaction, string commandText, CommandType commonType, params MySqlParameter[] parms)
        {
            PrepareCommand(command, connection, transaction, commandText, commonType, parms);

            DataSet ds = new DataSet();
            adapter.Fill(ds);
            if (commandText.IndexOf("@") > 0)
            {
                commandText = commandText.ToLower();
                int index = commandText.IndexOf("where ");
                if (index < 0)
                {
                    index = commandText.IndexOf("\nwhere");
                }
                if (index > 0)
                {
                    ds.ExtendedProperties.Add("SQL", commandText.Substring(0, index - 1));  //将获取的语句保存在表的一个附属数组里，方便更新时生成CommandBuilder
                }
                else
                {
                    ds.ExtendedProperties.Add("SQL", commandText);  //将获取的语句保存在表的一个附属数组里，方便更新时生成CommandBuilder
                }
            }
            else
            {
                ds.ExtendedProperties.Add("SQL", commandText);  //将获取的语句保存在表的一个附属数组里，方便更新时生成CommandBuilder
            }

            foreach (DataTable dt in ds.Tables)
            {
                dt.ExtendedProperties.Add("SQL", ds.ExtendedProperties["SQL"]);
            }

            command.Parameters.Clear();
            return ds;
        }

        static void PrepareCommand(DbCommand command, DbConnection connection, DbTransaction transaction, string commandText, CommandType commandType, MySqlParameter[] parms)
        {
            if (connection.State != ConnectionState.Open) connection.Open();

            command.Connection = connection;
            command.CommandTimeout = CommandTimeOut;
            command.CommandText = commandText; // 设置命令文本(存储过程名或SQL语句)
            command.CommandType = commandType; // 设置命令类型.

            if (transaction != null)
            {
                command.Transaction = transaction; // 分配事务
            }
            if (parms == null || parms.Length == 0) return;

            //预处理DbParameter参数数组，将为NULL的参数赋值为DBNull.Value;
            foreach (MySqlParameter parameter in parms)
            {
                if (parameter.Value != null) continue;
                if (parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input)
                {
                    parameter.Value = DBNull.Value;
                }
            }
            command.Parameters.AddRange(parms);
        }

        protected static DbDataReader ExecuteDataReader(DbConnection connection, DbCommand command, DbTransaction transaction,CommandType commandType, string commandText, params MySqlParameter[] parms)
        {
            PrepareCommand(command, connection, transaction, commandText, commandType, parms);
            var dbDataReader = command.ExecuteReader(CommandBehavior.CloseConnection);
            return dbDataReader;
        }
    }
}
